# ===============================================================================================================
#    PostScan - a plugin for Squeezebox Server 7.5 and greater
#
#    Executes SQL queries or commands against the Squeezebox Server
#	 Database after a music library scan.
#
#    Version 20110506.124207
#
#    Copyright (C) 2010 Gordon Harris
#
#    This program is free software; you can redistribute it and/or
#    modify it under the terms of the GNU General Public License,
#    version 2, which should be included with this software.
#


package Plugins::PostScan::Plugin;

use strict;

use base qw(Slim::Plugin::Base);

use Slim::Utils::Strings qw(string);
use Slim::Utils::Prefs;

if ( main::WEBUI ) {
	require Plugins::PostScan::Settings;
}

#Global Variables..

use vars qw(%g);

%g = (
	nAppVersion		=> 20110506.124207,		#version of this code..
	nSCVersion		=> GetSCVersion(),
	log				=> undef,				#log hash
	prefs			=> undef,				#preferences
);


#Get the SqueezeCenter Version, change it from 7.3.4 form into 7.34 and make it numeric..
#LIMITATION: this would see 7.4.1 as equal to 7.4.10
sub GetSCVersion {
	my $nSCVersion = $::VERSION;
	if (length($nSCVersion) > 3) {
		my $szMinor = substr($nSCVersion, 3);
		#remove any periods..
		$szMinor =~ s/\.//g;
	   $nSCVersion = substr($nSCVersion, 0, 3) . $szMinor;
	   }

	$nSCVersion = $nSCVersion * 1;

	return $nSCVersion;
}


sub initPlugin {
	my $class = shift;

	#Initialize global vars, prefs, log, etc..
	InitLog();
	InitPrefs();

	# Enable the settings page..
	if ( main::WEBUI ) {
		Plugins::PostScan::Settings->new($class);
	}
	
	# Monitor for scans..
	$g{log}->is_debug && $g{log}->debug("Activating db scan monitor..");
	Slim::Control::Request::subscribe( \&scanMonitor, [['rescan', 'wipecache']]);

	$g{log}->info("$class initialized..");

	$class->SUPER::initPlugin();

}


sub InitLog {
	if (!defined( $g{log} )) {
		$g{log} = Slim::Utils::Log->addLogCategory({
			'category'     => 'plugin.postscan',
			'defaultLevel' => 'DEBUG',
			'description'  => 'PLUGIN_POSTSCAN',
			});
	}
	$g{log}->is_debug && $g{log}->debug("Log initialized..");
}


sub InitPrefs {
	$g{log}->is_debug && $g{log}->debug("Initializing prefs..");

	if (!defined( $g{prefs} )) {
		$g{prefs} = preferences('plugin.postscan');
	} else {
		$g{log}->is_debug && $g{log}->debug("Prefs already defined!");
	}

	$g{prefs}->init({
		nAppVersion		=> $g{nAppVersion},
		bGlobalEnabled	=> 1,
		queries			=> [],
		});
	
	$g{log}->is_debug && $g{log}->debug("Prefs Initialized..");
}



# Scan Types:
# wipecache = 1
# scan for new = 2
# scan playlists = 4
# any scan  = 8
my $nCurrentScanType = 0;
# a ScanType of 8 indicates that the query should run for any type of scan..

sub scanMonitor {
	my $request = shift;
	my $client;
	my $cmd;
	my $bGlobalEnabled;
	my $nGlobalAllowedScanType;
	my @filter = ('rescan done');
	my $n = 0;
	my $bRet;
	my $msgformat;
	my $dispmsg;
	my $errmsg;

	#Plugins::PostScan::Settings::logHashElements($request);
	#Plugins::PostScan::Settings::logHashElements($request->{'_params'});

	$cmd = $request->getRequestString();
	$client = $request->client(); # || "unknown";

	# If SBS has finished scanning..ie the request string is 'rescan done'..

	if ( grep { $_ eq $cmd} @filter ) {
		$g{log}->is_debug && $g{log}->debug("scanMonitor called for client: ". ( $client ? $client->name() : 'no client' ) . ",  RequestString: $cmd");
		
		# Get the count of the queries
		$n = scalar(@{$g{prefs}->get('queries')});

		$g{log}->is_debug && $g{log}->debug("Query Count1 == $n");

		my $progress = Slim::Utils::Progress->new({ 
			'type'  => 'importer', 
			'name'  => 'postscan', 
			'total' => $n, 
			'bar'   => 0,
		});

		#$progress->total($n);
		
		$bGlobalEnabled = $g{prefs}->get('bGlobalEnabled');

		# If PostScan is globally disabled..

		if ( ! $bGlobalEnabled ) {
			$dispmsg = string('PLUGIN_POSTSCAN_GLOBALDISABLED_MSG');
			$g{log}->info($dispmsg);
			$n = -1;
			$progress->update($dispmsg, undef, $n);
			Slim::Utils::Timers::setTimer(undef, time() + 10, sub { $progress->final($n) if $progress; }, );
			#$progress->final($n);			
			return 0;
		}
		
		
		# Else, run the post scans!!
		
		for my $optname (qw( queries )) {

			$n = 0;

			# Existing saved queries..
			for my $opt (@{$g{prefs}->get($optname)}) {
				#$g{log}->is_debug && $g{log}->debug("Query #$n      name: $opt->{'name'}");
				#$g{log}->is_debug && $g{log}->debug("Query #$n     query: $opt->{'query'}");
				#$g{log}->is_debug && $g{log}->debug("Query #$n validated: $opt->{'validated'}");
				#$g{log}->is_debug && $g{log}->debug("Query #$n  scantype: $opt->{'scantype'}");
				
				# If the query is validated and enabled and (marked for any scan type || marked for the current scan type)
				
				if ( ($opt->{'validated'} && $opt->{'scantype'}) && ( ($opt->{'scantype'} == 8) || ($opt->{'scantype'} == $nCurrentScanType) ) ) {
					$msgformat = string('PLUGIN_POSTSCAN_RUN_MSG');
					$dispmsg = sprintf($msgformat, $opt->{'name'});
					$g{log}->info($dispmsg);
					$n++;

					# execute the query..
					($bRet, $errmsg) = performQuery2($opt->{'query'});

					# update the status..
					$progress->update($dispmsg, undef, $n);

				} else {
					$msgformat = string('PLUGIN_POSTSCAN_NOTRUN_MSG');
					$dispmsg = sprintf($msgformat, $opt->{'name'}, ( !$opt->{'validated'} ? string('PLUGIN_POSTSCAN_NOTVALIDATED') : string('PLUGIN_POSTSCAN_NOTENABLED') )	);
					$g{log}->info($dispmsg);
					$progress->update($dispmsg, undef, $n);
				}
			}

			$dispmsg = string('PLUGIN_POSTSCAN_DONE_MSG');
			$progress->update($dispmsg, undef, $n);

			$progress->final($n);

			return $n;
		}
	
	# else, this is a start-of-scan notifacation..
	
    } else {
		$g{log}->is_debug && $g{log}->debug("scanMonitor called for client: ". ( $client ? $client->name() : 'no client' ) . ",  Unfiltered RequestString: $cmd");
		if ($cmd eq 'wipecache') {
			$nCurrentScanType = 1;
		} elsif ($cmd eq 'rescan') {
			if ( $request->{'_params'}->{'_playlists'} eq 'playlists' ) {
				$nCurrentScanType = 4;
			} else {
				$nCurrentScanType = 2;
			}
		}
		if ($g{log}->is_debug) {
			my @aScanTypes = qw(disabled, wipecache, scan_for_new, notused, playlist, notused, notused, notused, any_type);
			my $szScanType = $aScanTypes[$nCurrentScanType];
			$g{log}->debug("Current scan type set to: $szScanType ($nCurrentScanType)");
		}
	}

	return 0;
}


#sub performQuery1 {
#    my $szSQL = shift;
#    #Code from AndyG
#
#	$g{log}->is_debug && $g{log}->debug("Preparing to test:\n$szSQL");
#
#    eval {
#        Slim::Schema->dbh->do($szSQL);
#    };
#    
#    if( $@ ) {
#        
#        $g{log}->error("Query execution error: $DBI::errstr");
#        $g{log}->error("Query:\n\n$szSQL\n\n..did not execute!");
#
#        #Plugins::PostScan::Settings::logHashElements($@);
#
#        eval {
#            #rollback($dbh);
#        };
#        return 0;
#    }
#    
#	$g{log}->is_debug && $g{log}->debug("Query succeeded!");
#
#    return 1;
#}


# http://cpansearch.perl.org/src/ADAMK/DBD-SQLite-1.30_03/README
  #Processing Multiple Statements At A Time
  #  DBI's statement handle is not supposed to process multiple statements at
  #  a time. So if you pass a string that contains multiple statements (a
  #  "dump") to a statement handle (via "prepare" or "do"), DBD::SQLite only
  #  processes the first statement, and discards the rest.
  #
  #  Since 1.30_01, you can retrieve those ignored (unprepared) statements
  #  via "$sth->{sqlite_unprepared_statements}". It usually contains nothing
  #  but white spaces, but if you really care, you can check this attribute
  #  to see if there's anything left undone. Also, if you set a
  #  "sqlite_allow_multiple_statements" attribute of a database handle to
  #  true when you connect to a database, "do" method automatically checks
  #  the "sqlite_unprepared_statements" attribute, and if it finds anything
  #  undone (even if what's left is just a single white space), it repeats
  #  the process again, to the end.
  #

sub performQuery2 {
	my $szSQL = shift;

	my $dbh;    
	my $sth;
	my @sio;
	my @queries;
	my $query;
	my $szErrMsg = '';
	my $nExecuted = 0;


	$dbh = Slim::Schema->storage->dbh();
	
	@sio = Slim::Schema::sourceInformation();
	#$driver, $source, $username, $password
	#sourceInfo: SQLite dbi:SQLite:dbname=/var/lib/squeezeboxserver_trunk/cache/squeezebox.db squeezecenter squeezecenter

	# Create the SQLite helper functions
	if ($sio[0] eq 'SQLite') {
		

		# SQLite understands SUBSTR but not SUBSTRING..
		# Impliment a SQL SUBSTRING function, but with behavior like: http://perldoc.perl.org/functions/substr.html
		# substr EXPR,OFFSET,LENGTH,REPLACEMENT
		#$g{log}->is_debug && $g{log}->debug("Creating SQLite query helper function: SUBSTRING() \n");
		$dbh->sqlite_create_function( 'SUBSTRING', -1,
			sub {
				my ($szExpr, $nOffset, $nLength, $szReplacement) = @_;
				my $szNewStr = 	substr($szExpr, $nOffset, $nLength, $szReplacement);
				return $szNewStr;
			} );

		# SQLite has no INSTR function..
		# INSTR(str,substr,position)
		# Impliment one using the perl index function..
		# index() returns the position of the first occurrence of substring substr in string str.
		# http://perldoc.perl.org/functions/index.html
		#$g{log}->is_debug && $g{log}->debug("Creating SQLite query helper function: INSTR() \n");
		$dbh->sqlite_create_function( 'INSTR', -1,
			sub {
				my ($szStr, $szSubstr, $nPosition) = @_;
				my $nIndex = index($szStr, $szSubstr, $nPosition);
				return $nIndex;
			} );
		

		# SQLite's REGEXP function only works with SELECT.
		# Impliment a REGEXP-like function that uses regular expressions to modify data..
		#$g{log}->is_debug && $g{log}->debug("Creating SQLite query helper function: PSREPLACE() \n");
		$dbh->sqlite_create_function( 'PSREPLACE', 2,
			sub {
				my ($szStr, $szExp) = @_;
			
				my $szNewStr = $szStr;
				
				my $szDebug;
			
				my @exp = split( /\//, $szExp);
			
				# TODO: add support for s///i ???  This is already supported via s/(?i)pattern/replace/
				
				if ( (scalar(@exp) < 3) || ($exp[0] ne 's') ) {
					$g{log}->error("PSREPLACE bad pattern: $szExp !!\n");
				} else {
					if (scalar(@exp) >= 4 && $exp[3] eq 'g') {
						#This seems like a kludge...what's really going on here??
						if ($exp[2] =~ m/\$\d/) {
							if ($g{log}->is_debug) { $szDebug = "s\/$exp[1]\/$exp[2]\/gee"; }
							$szNewStr =~ s/$exp[1]/$exp[2]/gee;
						} else {
							if ($g{log}->is_debug) { $szDebug = "s\/$exp[1]\/$exp[2]\/ge"; }
							$szNewStr =~ s/$exp[1]/$exp[2]/ge;
						}
					} else {
						if ($exp[2] =~ m/\$\d/) {
							if ($g{log}->is_debug) { $szDebug = "s\/$exp[1]\/$exp[2]\/ee"; }
							$szNewStr =~ s/$exp[1]/$exp[2]/ee;
						}else {
							if ($g{log}->is_debug) { $szDebug = "s\/$exp[1]\/$exp[2]\/e"; }
							$szNewStr =~ s/$exp[1]/$exp[2]/e;
						}
					}
					$g{log}->is_debug && $g{log}->debug("PSREPLACE $szStr \=\~ $szDebug == $szNewStr");
				}
				
				return $szNewStr;
			} );

		# Impliment PSMATCH, a REGEXP like function, but with a little more matching horsepower.
		# PSMATCH(STR, EXP)
		# If STR is matched by EXP, PSMATCH returns STR, else, it returns an empty string ('').
		# Use this with WHERE data = PSMATCH(data, 'pattern')
		$dbh->sqlite_create_function( 'PSMATCH', 2,
			sub {
				my ($szStr, $szExp) = @_;
				my $szNewStr = $szStr;
				my $bRet = '';
			
				#$g{log}->is_debug && $g{log}->debug("input  == $szNewStr");
				#$g{log}->is_debug && $g{log}->debug("regex  == $szExp");
			
				my @exp = split( /\//, $szExp);
			
				if ( (scalar(@exp) < 2) || ($exp[0] ne 'm') ) {
					$g{log}->error("PSMATCH bad pattern: $szExp !!\n");
				} else {
					if (scalar(@exp) >= 3 && $exp[2] eq 'g') {
						if ($szNewStr =~ m/$exp[1]/g) {
							$bRet = $szNewStr;
						}
					} else {
						if ($szNewStr =~ m/$exp[1]/) {
							$bRet = $szNewStr;
						}
					}
				}
				
				$g{log}->is_debug && $g{log}->debug( "PSMATCH: \'$szNewStr\' \=\~ m\/$exp[1]\/" . ((scalar(@exp) >= 3 && $exp[2] eq 'g ') ? 'g' : ' ') .  "== " . ($bRet ? "MATCH!" : "NO MATCH!") );

				return $bRet;
			} );

		
	} else {
		$g{log}->is_debug && $g{log}->debug("DBDriver: $sio[0]\n");
	}

	$g{log}->is_debug && $g{log}->debug("SQL statment(s)\n$szSQL\n\n");
	
	@queries = split(';', $szSQL);

	foreach $query (@queries) {
		
		#remove line breaks..
		$query =~ s/\n/ /g;
		
		#if the statment is empty..skip it..
		if ($query =~ m/^\s+$/) {
			next;
		}

		$query = $query . ';';
		
		#Code adapted from erland:
		eval {
			$sth = $dbh->prepare( $query );
		};
	
		if( $@ ne "" ) {
			$szErrMsg = $DBI::errstr;
			$g{log}->error("\nQuery execution error: $szErrMsg");
			$g{log}->error("Query:\n\n$query\n\n..is not executable!");
			return (0, $szErrMsg);
		}

		$g{log}->is_debug && $g{log}->debug("Executing:\n\'$query\'");

		eval {
			$sth->execute();
			#commit($dbh);
		};

		if( $@ ne "" ) {
			$szErrMsg = $DBI::errstr;
			$g{log}->error("\nQuery execution error: $szErrMsg");
			$g{log}->error("Query:\n\n$$query\n\n..did not execute!");
		
			eval {
				rollback($dbh);
			};
		
			return (0, $szErrMsg);
		} else {
			$nExecuted++;
		}
		
		$g{log}->is_debug && $g{log}->debug("Query #$nExecuted execution succeeded!");

	}
	
	return ($nExecuted, '');
}

sub getIcon {
	my ( $class, $url ) = @_;

	return Plugins::PostScan::Plugin->_pluginDataFor('icon');
}


sub getDisplayName {

	return 'PLUGIN_POSTSCAN';
}

sub shutdownPlugin {
	my ($class) = @_;

	$g{log}->is_debug && $g{log}->debug("Deactivating db scan monitor..");
	Slim::Control::Request::unsubscribe( \&scanMonitor, [['rescan', 'wipecache']]);

}

1;
